'''管理网络图片的脚本.主要是管理上传到各种匿名图床的图片.
使用自制的UploadManager类进行上传,而这个脚本则是管理上传后的数据库记录.
数据库用sqlite3.

主要类是WebPicManager.
对应的sqlite3数据库文件是WebPicData.sqlite3,对应的表示WebPic,每一条记录为:
id(自增key),name, url, tags, data

上传图片的类则是自己写的UploadManager.

WebPicManager类主要的方法是:
upload:上传
以及各种查找,查找结果记录在searchResult.txt文本文件中,其实用可视化工具更实在,
所以没写太多.查找结果用_handleSearchResult显示与记录.可以写更多的查找方法.
searchByTag:按标签查找
getAllData:查找全部
暂时没有写修改记录的方法,还是用可视化工具吧.用SQLiteSpy新建数据库,查看,修改.

最终更新日期:
2019/7/8
'''

import os
import sys
import sqlite3

import UploadManager

class WebPicManager:

  ## 初始化,尝试新建表
  def __init__(self):
    ## 连接数据库
    self._dbFile = os.path.join(sys.path[0], r'WebPicData.sqlite3')
    self._dbConn = sqlite3.connect(self._dbFile)
    ## 生成上传管理器类
    self._uploadManager = UploadManager.UploadManager()
    ## 记录查询结果的文件位置
    self._searchResultFile = os.path.join(sys.path[0], r'searchResult.txt')
    ## 规定记录图片的表名
    self._tableName = 'WebPic'
    ## 尝试创建数据表''
    try:
      curs = self._dbConn.cursor()
      query = '''create table {}(ID INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL, 
        url TEXT NOT NULL, 
        tags TEXT NOT NULL, 
        data MEMOT NOT NULL
      );'''.format(self._tableName)
      curs.execute(query)
      curs.fetchall()
      self._dbConn.commit()
      print('Table {} created!'.format(self._tableName))
    except:
      print('Table {} already existed!'.format(self._tableName))
    print('')
    return
    
  ## 上传并记录
  def upload(self, domain, filepath, tags = []):
    result = self._uploadManager.upload(domain, filepath, tags)
    curs = self._dbConn.cursor()
    if (result):
      vals = (result['name'], result['url'], result['tags'], result['data'])
      query = '''INSERT INTO {} (name, url, tags, data) 
        VALUES (?, ?, ?, ?);
      '''.format(self._tableName)
      curs.execute(query,vals)
      curs.fetchall()
      self._dbConn.commit()
    return
  
  ## 根据搜索结果,生成文本并记录.
  ## searchResult是提交sql语句的返回,select *;
  ## title则是自定义的文本记录标题
  def _handleSearchResult(self, searchResult, title):
    ## 文本结果表,每个搜索结果占一个项
    textList = [] 
    for result in searchResult:
      ## 如果url空,可能是测试遗留,不记录
      if (result[2].strip() == ''):
        continue
      text = 'name: {0}\nurl: {1}\ntags: {2}\ndata: {3}'.format(
        result[1], result[2], result[3], result[4]
      )
      textList.append(text)
    ## 从结果表生成结果字符串,输出并记录
    totalText = '='*10+' {} '.format(title)+'='*10+'\n'
    totalText += ('\n'+'='*20+'\n').join(textList)
    print(totalText)
    with open (self._searchResultFile, 'w') as f:
      f.write(totalText)
    return
  
  ##* 各种查找方法 *##
  
  ## 按tag查找图片记录
  def searchByTag(self, tag):
    ## 查找
    curs = self._dbConn.cursor()
    result = curs.execute('SELECT * FROM {0} where tags like "%{1},%"'.format(
      self._tableName, tag)
    )
    searchResult = curs.fetchall()
    title = 'Tag search:{}'.format(tag)
    self._handleSearchResult(searchResult, title)
    return
  
  ## 显示全部记录
  def getAllData(self):
    curs = self._dbConn.cursor()
    result = curs.execute('SELECT * FROM {0}'.format(
      self._tableName)
    )
    searchResult = curs.fetchall()
    title = 'All data:'
    self._handleSearchResult(searchResult, title)
    return

  ## 最后要关闭
  def close(self):
    self._dbConn.close()
    return
    
  ##* 各种测试方法: *##
  
  def _sql_test1(self):
    curs = self._dbConn.cursor()
    query = '''INSERT INTO {} (name, url, tags, data) 
      VALUES (?, ?, ?, ?);
    '''.format(self._tableName)
    vals = ('test_name002', 'test_url002', 'aa,test_tags002,test-tags003', 
      'test_data002')
    curs.execute(query,vals)
    curs.fetchall()
    self._dbConn.commit()
    print('_sql_test1 successed')
    return
    
  def _sql_test2(self):
    curs = self._dbConn.cursor()
    result = curs.execute(
      'SELECT * FROM {} where tags like "%test_tags002,%"'.format(
        self._tableName)
    )
    print(curs.fetchall())
    
  def _uploadTest(self):
    filepath = r'D:\MyDocument\MyPic\其他\izumo3立绘集\natsumi_b.gif'
    domain = 'imgurl.org'
    tags = ['izumo3', 'test', '立绘']
    self.upload(domain, filepath, tags = tags)
    return

if __name__ == '__main__':
  wpm = WebPicManager()
  #wpm._sql_test1()
  #wpm._sql_test2()
  #wpm._uploadTest()
  #wpm.searchByTag('立绘')
  #wpm.getAllData()
  
  ## 上传代码,实际上传时让uploadFlag = True,并指定uploadTable的内容.
  uploadFlag = False
  ## 上传表的每个域名指定一堆上传文件与tags
  uploadTable = {
    'imgurl.org':[
      ( 
        r'D:\MyDocument\MyPic\其他\izumo3立绘集\natsumi_b.gif', 
        ['izumo3', 'test', '立绘']
      ),
      ( 
        r'D:\MyDocument\MyPic\其他\izumo3立绘集\natsumi_a.gif', 
        ['izumo3', 'test', '立绘']
      )
    ]
  }
  if (uploadFlag):
    x = input('即将上传,输入0+回车继续,其他+回车取消')
    if (x == '0'):
      print('上传开始!')
      for domain in uploadTable:
        uploadList = uploadTable[domain]
        for uploadMission in uploadList:
          wpm.upload(domain, uploadMission[0], uploadMission[1])
    else:
      print('上传取消!')
  ## 上传代码结束
  
  ## 最后查找测试
  wpm.searchByTag('izumo3')
  wpm.close()